package aswerGenerator;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

import dbUnit.MysqlConnect;

import lexicalUnit.Environ;

public class GetAnswerData {

	public MysqlConnect msc;
	public Environ env = null;
	private String answer = null;
	private int count = 0;

	public String tmpkeyWord = "";

	public GetAnswerData(Environ env) {
		this.env = env;
		msc = new MysqlConnect();
		tmpkeyWord = this.env.getKeyword();
	}
	
	private void addAnswer (String ans){
		if (answer == null)
			answer = ans;
		else if (answer.contains("and"))
			answer =  ans + ", " + answer;
		else answer = ans + " and " + answer;
	}
	
	private String genAnswer (Statement stat, String query ,String key, String error) throws SQLException{
		answer = null;
		count = 0;
		ResultSet results = stat.executeQuery(query);
		while (results.next()) {
			addAnswer(results.getString(key));
			count++;
		}
		results.close();
		if (answer==null)
			throw new EmptyQueryException(error);
		env.setNumberOfAnswers(count);
		return answer;
	}
	
	public String fetchAll() {
		if (env.getTarget()==Environ.MANDATORY_COURSE)
			return fetchMandatoryCourse();
		if (env.getTarget()==Environ.OPTIONAL_COURSE)
			return fetchOptionalCourse();
		if (env.getTarget()==Environ.COURSE)
			return fetchCourse();
		throw new NotFoundInDatabaseException("I need more data, please be more specific.");			
	}
	
	public String whatIsIt(Statement stat, ResultSet results) throws SQLException{
		String selectQuery;
		// if keyword is campus
		selectQuery = "Select * from campus where campus_name = \"" + tmpkeyWord + "\"";
		results = stat.executeQuery(selectQuery);
		if (results.next()){
			env.setKeywordClass(Environ.CAMPUS);
			results.close();
			return "a campus";
		}
		results.close();
		// if keyword is Building
		selectQuery = "Select * from building where building_name = \"" + tmpkeyWord + "\"";
		results = stat.executeQuery(selectQuery);
		if (results.next()){
			env.setKeywordClass(Environ.BUILDING);
			results.close();
			return "a building on " + genAnswer(stat,"SELECT campus_fk from building where building_name = \"" + tmpkeyWord + "\"","campus_fk",tmpkeyWord  + " is a building");
		}
		results.close();

		// if keyword is Room
		selectQuery = "Select * from room where room_name = \"" + tmpkeyWord + "\"";
		results = stat.executeQuery(selectQuery);
		if (results.next()){
			results.close();
			env.setKeywordClass(Environ.ROOM);
			return "the room " + genAnswer(stat,"SELECT room_code from room where room_name = \"" + tmpkeyWord + "\"","room_code","This is a room");
		}
		results.close();

		//if keyword room code
		selectQuery = "Select * from room where room_code = \"" + tmpkeyWord + "\"";
		results = stat.executeQuery(selectQuery);
		if (results.next()){
			results.close();
			env.setKeywordClass(Environ.CODE);
			return "the room " + genAnswer(stat,"SELECT room_name from room where room_code = \"" + tmpkeyWord + "\"","room_name","This is a room");
		}
		results.close();

		//teacher
		selectQuery = "Select * from teacher where teacher_name = \"" + tmpkeyWord + "\"";
		results = stat.executeQuery(selectQuery);
		if (results.next()){
			results.close();
			env.setKeywordClass(Environ.PERSON);
			return "a teacher";
		}
		results.close();
		
		//course
		selectQuery = "Select * from course where course_code = \"" + tmpkeyWord + "\"";
		results = stat.executeQuery(selectQuery);
		if (results.next()){
			results.close();
			env.setKeywordClass(Environ.COURSE);
			return "the code of the course " + genAnswer(stat,"SELECT course_name from course where course_code = \"" + tmpkeyWord + "\"","course_name","This is a course");
		}
		results.close();
		
		selectQuery = "Select * from course where course_name = \"" + tmpkeyWord + "\"";
		results = stat.executeQuery(selectQuery);
		if (results.next()){
			results.close();
			return "a name of a course";
		}
		results.close();
		
		//program
		selectQuery = "Select * from program where program_name = \"" + tmpkeyWord + "\"";
		results = stat.executeQuery(selectQuery);
		if (results.next()){
			results.close();
			env.setKeywordClass(Environ.PROGRAM);
			return "a " + genAnswer(stat,"SELECT program_level from program where program_name = \"" + tmpkeyWord + "\"","program_level","This is a program") + " program";
		}
		results.close();
		
		selectQuery = "Select * from program where program_code = \"" + tmpkeyWord + "\"";
		results = stat.executeQuery(selectQuery);
		if (results.next()){
			results.close();
			return "the " + genAnswer(stat,"SELECT program_name from program where program_code = \"" + tmpkeyWord + "\"","program_name","This is a program") + " program";
		}
		results.close();
		
		throw new NotFoundInDatabaseException(tmpkeyWord + " doesn't exists");
	}
	
	public String fetchThing(){
		String selectQuery = "";
		ResultSet results = null;
		try {
			msc.dbConnect();
			Statement stat = msc.conn.createStatement();
			
			if (env.getVerbKeyword()==Environ.VERB_IS)
				return whatIsIt(stat, results);	
			if (env.getVerbKeyword()==Environ.VERB_TEACH){
				selectQuery = "Select * from teacher where teacher_name = \"" + tmpkeyWord + "\"";
				results = stat.executeQuery(selectQuery);
				if (results.next()){
					results.close();
					return genAnswer(stat,"SELECT distinct course_name from course where course_teacher_fk = \"" + tmpkeyWord + "\"","course_name","This teacher does not teach any courses");
				}
				results.close();
			}
			
		} catch (SQLException e) {
			msc.dbClose();
			throw new NotFoundInDatabaseException(tmpkeyWord + " doesn't exists");
		}
		throw new NotFoundInDatabaseException(tmpkeyWord + " doesn't exists");
	}
	
	public String fetchCampus() {
		String selectQuery = "";
		ResultSet results = null;
		try {
			msc.dbConnect();
			Statement stat = msc.conn.createStatement();
			
			// if keyword is Building
			selectQuery = "Select * from building where building_name = \"" + tmpkeyWord + "\"";
			results = stat.executeQuery(selectQuery);
			if (results.next()){
				results.close();
				return genAnswer(stat,"SELECT campus_fk from building where building_name = \"" + tmpkeyWord + "\"","campus_fk","This building isn't on any campus of the university");
			}
			results.close();
						
			// if keyword is Room
			selectQuery = "Select * from room where room_name = \"" + tmpkeyWord + "\"";
			results = stat.executeQuery(selectQuery);
			if (results.next()){
				results.close();
				return genAnswer(stat,"SELECT b.campus_fk from room r, building b where r.building_fk = b.building_name and r.room_name = \"" + tmpkeyWord + "\"","b.campus_fk","This room isn't on any campus of the university");
			}
			results.close();
			
			//if keyword room code
			selectQuery = "Select * from room where room_code = \"" + tmpkeyWord + "\"";
			results = stat.executeQuery(selectQuery);
			if (results.next()){
				results.close();
				return genAnswer(stat,"SELECT b.campus_fk from room r, building b where r.building_fk = b.building_name and r.room_code = \"" + tmpkeyWord + "\"","b.campus_fk","This room isn't on any campus of the university");
			}
			results.close();
			
			
		} catch (SQLException e) {
			msc.dbClose();
			throw new NotFoundInDatabaseException(tmpkeyWord + " doesn't belong to any campus");

		}
		throw new NotFoundInDatabaseException(tmpkeyWord + " doesn't belong to any campus");
	}		
	
	public String fetchBuilding() throws NotFoundInDatabaseException {
		String selectQuery = "";
		ResultSet results = null;
		try {
			msc.dbConnect();
			Statement stat = msc.conn.createStatement();

			// if keyword is campus
			selectQuery = "Select * from campus where campus_name = \"" + tmpkeyWord + "\"";
			results = stat.executeQuery(selectQuery);
			if (results.next()){
				results.close();
				return genAnswer(stat,"SELECT building_name FROM building where campus_fk = \"" + tmpkeyWord + "\"","building_name","This campus doesn't have any buildings");
			}
			results.close();

			// if keyword is Room
			selectQuery = "Select * from room where room_name = \"" + tmpkeyWord + "\"";
			results = stat.executeQuery(selectQuery);
			if (results.next()){
				results.close();
				return genAnswer(stat,"SELECT concat (room_floor, \" \", building_fk) as location FROM room where room_name = \"" + tmpkeyWord + "\"","location","This room isn't on any campus of the university");
			}
			results.close();
			
			//if keyword room code
			selectQuery = "Select * from room where room_code = \"" + tmpkeyWord + "\"";
			results = stat.executeQuery(selectQuery);
			if (results.next()){
				results.close();
				return genAnswer(stat,"SELECT concat (room_floor, \" \", building_fk) as location FROM room where room_code = \"" + tmpkeyWord + "\"","location","This room isn't on any campus of the university");
			}
			results.close();
		} catch (SQLException e) {
			msc.dbClose();
			throw new NotFoundInDatabaseException(tmpkeyWord + " doesn't belong to any building");

		}
		throw new NotFoundInDatabaseException(tmpkeyWord + " doesn't belong to any building");
	}
	
	public String fetchRoom() {
		String selectQuery = "";
		ResultSet results = null;
		try {
			msc.dbConnect();
			Statement stat = msc.conn.createStatement();
			
			// if keyword is Building
			selectQuery = "Select * from building where building_name = \"" + tmpkeyWord + "\"";
			results = stat.executeQuery(selectQuery);
			if (results.next()){
				results.close();
				return genAnswer(stat,"SELECT room_code from room where building_fk = \"" + tmpkeyWord + "\"","room_code","This building doesn't have any room");
			}
			results.close();
						
			// if keyword is Room
			selectQuery = "Select * from room where room_name = \"" + tmpkeyWord + "\"";
			results = stat.executeQuery(selectQuery);
			if (results.next()){
				results.close();
				return genAnswer(stat,"SELECT distinct room_code from room where room_name = \"" + tmpkeyWord + "\"","room_code",tmpkeyWord);
			}
			results.close();
			
			//if keyword room code
			selectQuery = "Select * from room where room_code = \"" + tmpkeyWord + "\"";
			results = stat.executeQuery(selectQuery);
			if (results.next()){
				results.close();
				return genAnswer(stat,"SELECT room_name from room where room_code = \"" + tmpkeyWord + "\"","room_name",tmpkeyWord);
			}
			results.close();
			
			//teacher
			selectQuery = "Select * from teacher where teacher_name = \"" + tmpkeyWord + "\"";
			results = stat.executeQuery(selectQuery);
			if (results.next()){
				results.close();
				return genAnswer(stat,"SELECT room_fk from teacher where teacher_name = \"" + tmpkeyWord + "\"","room_fk","The teacher " + tmpkeyWord + " doesn't have any room assigned");
			}
			results.close();
			
			//course name
			selectQuery = "Select * from course where course_name = \"" + tmpkeyWord + "\"";
			results = stat.executeQuery(selectQuery);
			if (results.next()){
				results.close();
				return genAnswer(stat,"SELECT course_rom_fk from course where course_name = \"" + tmpkeyWord + "\"","course_rom_fk","This course does not have an assigned room");
			}
			results.close();
			
			//course code
			selectQuery = "Select * from course where course_code = \"" + tmpkeyWord + "\"";
			results = stat.executeQuery(selectQuery);
			if (results.next()){
				results.close();
				return genAnswer(stat,"SELECT course_rom_fk from course where course_code = \"" + tmpkeyWord + "\"","course_rom_fk","No room assiganed to " + tmpkeyWord);
			}
			results.close();
				
		} catch (SQLException e) {
			msc.dbClose();
			throw new NotFoundInDatabaseException(tmpkeyWord + " doesn't belong to any room");

		}
		throw new NotFoundInDatabaseException(tmpkeyWord + " doesn't belong to any room");
	}

	/*
	 * this will get the data for PLace with Where question test
	 * 
	 * @ismail
	 */
	public String fetchPlace() throws NotFoundInDatabaseException {

		String selectQuery = "";
		ResultSet results = null;

		String tmp = "";
		try {
			msc.dbConnect();
			Statement stat = msc.conn.createStatement();
			
			// if keyword is Tecaher Name
			if (env.getVerbKeyword()==Environ.VERB_TEACH){
				selectQuery = "Select * from teacher where teacher_name = \"" + tmpkeyWord + "\"";
				results = stat.executeQuery(selectQuery);
				if (results.next()){
					results.close();
					return genAnswer(stat,"SELECT distinct course_rom_fk from course where course_teacher_fk = \"" + tmpkeyWord + "\"","course_rom_fk","This teacher does not teach any courses");
				}
				results.close();
			}else{
				selectQuery = "Select * from teacher";
				results = stat.executeQuery(selectQuery);
				while (results.next()) {
	
					tmp = results.getString("teacher_name");
	
					if (tmpkeyWord.equalsIgnoreCase(tmp)) {
						env.setKeywordClass(env.ROOM);
						return results.getString("room_fk");
					}
				}
				results.close();
			}

			// if keyword is campus
			selectQuery = "Select * from campus";
			results = stat.executeQuery(selectQuery);
			while (results.next()) {
				tmp = results.getString("campus_name");
				if (tmpkeyWord.equalsIgnoreCase(tmp)) {
					env.setKeywordClass(Environ.CAMPUS);
					return results.getString("campus_address");
				}
			}
			results.close();

			// if keyword is Building
			selectQuery = "Select * from building";
			results = stat.executeQuery(selectQuery);
			while (results.next()) {

				tmp = results.getString("building_name");

				if (tmpkeyWord.equalsIgnoreCase(tmp)) {
					env.setKeywordClass(env.BUILDING);
					return results.getString("building_adress");
				}
			}
			results.close();

			// if keyword is Room
			selectQuery = "Select * from room";
			results = stat.executeQuery(selectQuery);
			while (results.next()) {

				tmp = results.getString("room_name");

				if (tmpkeyWord.equalsIgnoreCase(tmp)) {
					env.setKeywordClass(env.ROOM);
					return results.getString("building_fk");
				}

				tmp = results.getString("room_code");

				if (tmpkeyWord.equalsIgnoreCase(tmp)) {
					env.setKeywordClass(env.ROOM);
					return results.getString("building_fk");
				}
			}
			results.close();

			// if keyword is course Name
			selectQuery = "Select * from course";
			results = stat.executeQuery(selectQuery);
			while (results.next()) {

				tmp = results.getString("course_name");

				if (tmpkeyWord.equalsIgnoreCase(tmp)) {
					env.setKeywordClass(env.ROOM);
					return results.getString("course_rom_fk");
				}

				tmp = results.getString("course_code");

				if (tmpkeyWord.equalsIgnoreCase(tmp)) {
					env.setKeywordClass(env.ROOM);
					return results.getString("course_rom_fk");
				}

			}
			results.close();
			msc.dbClose();

			throw new NotFoundInDatabaseException("Did not find any place related with " + tmpkeyWord);

		} catch (Exception e) {
			throw new NotFoundInDatabaseException("Did not find any place related with " + tmpkeyWord);

		} finally {
			msc.dbClose();
		}

		// return "Not is Database";
	}

	// -----------------------------------------------------------------------------------------------------------------------
	/*
	 * this will get the data for Person
	 * 
	 * @ismailprogram name, course name, course code, room
	 */
	public String fetchPerson() throws NotFoundInDatabaseException {

		String selectQuery = "";
		ResultSet results = null;

		String tmp = "";
		try {
			msc.dbConnect();
			Statement stat = msc.conn.createStatement();

			//program name
			selectQuery = "Select * from program where program_name = \"" + tmpkeyWord + "\"";
			results = stat.executeQuery(selectQuery);
			if (results.next()){
				results.close();
				return genAnswer(stat,"SELECT program_teacher_fk from program where program_name = \"" + tmpkeyWord + "\"","program_teacher_fk","There is no one responsible for " + tmpkeyWord);
			}
			results.close();
			
			//program code
			selectQuery = "Select * from program where program_code = \"" + tmpkeyWord + "\"";
			results = stat.executeQuery(selectQuery);
			if (results.next()){
				results.close();
				return genAnswer(stat,"SELECT program_teacher_fk from program where program_code = \"" + tmpkeyWord + "\"","program_teacher_fk","There is no one responsible for " + tmpkeyWord);
			}
			results.close();			
			
			// if keyword is Course code
			
			//course name
			selectQuery = "Select * from course where course_name = \"" + tmpkeyWord + "\"";
			results = stat.executeQuery(selectQuery);
			if (results.next()){
				results.close();
				if (env.getPeriod()==null)
					throw new PeriodNotFoundException("Course period needed to get the teacher of the course");
				return genAnswer(stat,"SELECT course_teacher_fk from course where course_period = \"" + env.getPeriod() +"\" AND course_name = \"" + tmpkeyWord + "\"","course_teacher_fk","This course is not given in period " + env.getPeriod());
			}
			results.close();
			
			//course code
			selectQuery = "Select * from course where course_code = \"" + tmpkeyWord + "\"";
			results = stat.executeQuery(selectQuery);
			if (results.next()){
				results.close();
				return genAnswer(stat,"SELECT course_teacher_fk from course where course_code = \"" + tmpkeyWord + "\"","course_teacher_fk","No teacher assiganed to " + tmpkeyWord);
			}
			results.close();

			// if keyword is Room
			selectQuery = "Select * from teacher";
			results = stat.executeQuery(selectQuery);
			while (results.next()) {

				tmp = results.getString("room_fk");

				if (tmpkeyWord.equalsIgnoreCase(tmp)) {
					env.setKeywordClass(env.ROOM);
					return results.getString("teacher_name");
				}
			}
			results.close();

			throw new NotFoundInDatabaseException("No teacher related with " + tmpkeyWord);

		} catch (SQLException e) {
			throw new NotFoundInDatabaseException("No teacher realated with " + tmpkeyWord);
		} finally {
			msc.dbClose();
		}
	}

	// -----------------------------------------------------------------------------------------------------------------------
	/*
	 * this will get the data for Period from course, course code
	 * 
	 * @ismaile
	 */
	public String fetchPeriod() throws NotFoundInDatabaseException {

		String selectQuery = "";
		ResultSet results = null;

		String tmp = "";
		try {
			msc.dbConnect();
			Statement stat = msc.conn.createStatement();
			// if keyword is Course
			selectQuery = "Select * from course";
			results = stat.executeQuery(selectQuery);
			while (results.next()) {

				// course name
				tmp = results.getString("course_name");
				if (tmpkeyWord.equalsIgnoreCase(tmp)) {
					env.setKeywordClass(env.COURSE);
					return results.getString("course_period");
				}

				// course code
				tmp = results.getString("course_code");
				if (tmpkeyWord.equalsIgnoreCase(tmp)) {
					env.setKeywordClass(env.COURSE);
					return results.getString("course_period");
				}

			}
			results.close();

			throw new NotFoundInDatabaseException("The course " + tmpkeyWord + " doesn't exists");

		} catch (Exception e) {
			throw new NotFoundInDatabaseException("The course " + tmpkeyWord + " doesn't exists");
		} finally {
			msc.dbClose();
		}

	}

	// -----------------------------------------------------------------------------------------------------------------------
	/*
	 * this will get the data for Program from teacher, course name,
	 * course_code,credits, level
	 * 
	 * @ismail
	 */
	public String fetchProgram() throws NotFoundInDatabaseException {

		String selectQuery = "";
		ResultSet results = null;

		String tmp = "";
		String tmpStore = "";

		int count = 0;

		try {
			msc.dbConnect();
			Statement stat = msc.conn.createStatement();

			// if keyword is course_code
			selectQuery = "Select * from program_course";
			results = stat.executeQuery(selectQuery);
			while (results.next()) {

				tmp = results.getString("course_fk");
				if (tmpkeyWord.equalsIgnoreCase(tmp)) {
					env.setKeywordClass(env.COURSE);
					return results.getString("program_name");
				}

			}
			results.close();

			//teacher
			selectQuery = "Select * from teacher where teacher_name = \"" + tmpkeyWord + "\"";
			results = stat.executeQuery(selectQuery);
			if (results.next()){
				results.close();
				return genAnswer(stat,"SELECT program_name from program where program_teacher_fk = \"" + tmpkeyWord + "\"","program_name","The teacher " + tmpkeyWord + " isn't responsible for any program");
			}
			results.close();

			//level
			selectQuery = "Select * from program where program_level = \"" + tmpkeyWord + "\"";
			results = stat.executeQuery(selectQuery);
			if (results.next()){
				results.close();
				return genAnswer(stat,"SELECT program_name from program where program_level = \"" + tmpkeyWord + "\"","program_name","No " + tmpkeyWord + " programs found.");
			}
			results.close();

			msc.dbClose();

			if (!tmpStore.equals(""))
				return tmpStore;
			else
				throw new NotFoundInDatabaseException("No programs related to " + tmpkeyWord);

		} catch (Exception e) {
			throw new NotFoundInDatabaseException("No programs related to " + tmpkeyWord);
		} finally {
			msc.dbClose();
		}
	}

	// -----------------------------------------------------------------------------------------------------------------------
	/*
	 * this will get the data for Course from program , code , course name,
	 * teacher, level, credits, period level
	 * 
	 * @ismail
	 */
	public String fetchCourse() throws NotFoundInDatabaseException {

		String selectQuery = "";
		ResultSet results = null;
		String tmp = "";
		String tmpStore = "";
		int count = 0;

		try {
			msc.dbConnect();
			Statement stat = msc.conn.createStatement();
			
			if (tmpkeyWord==null){
				if (env.getPeriod()==null)
					throw new PeriodNotFoundException("Any period was specified");
				throw new ProgramNotFoundException("Any program was specified");
			}

			//course name
			selectQuery = "Select * from course where course_name = \"" + tmpkeyWord + "\"";
			results = stat.executeQuery(selectQuery);
			if (results.next()){
				results.close();
				return genAnswer(stat,"SELECT course_code from course where course_name = \"" + tmpkeyWord + "\"","course_code","No code assiganed to " + tmpkeyWord);
			}
			results.close();
			
			//course code
			selectQuery = "Select * from course where course_code = \"" + tmpkeyWord + "\"";
			results = stat.executeQuery(selectQuery);
			if (results.next()){
				results.close();
				return genAnswer(stat,"SELECT distinct course_name from course where course_code = \"" + tmpkeyWord + "\"","course_name","No name assiganed to " + tmpkeyWord);
			}
			results.close();
			
			//teacher
			selectQuery = "Select * from teacher where teacher_name = \"" + tmpkeyWord + "\"";
			results = stat.executeQuery(selectQuery);
			if (results.next()){
				results.close();
				return genAnswer(stat,"SELECT distinct course_name from course where course_teacher_fk = \"" + tmpkeyWord + "\"","course_name","The teacher " + tmpkeyWord + " doesn't teach any course");
			}
			results.close();

			//level
			selectQuery = "Select * from course where course_level = \"" + tmpkeyWord + "\"";
			results = stat.executeQuery(selectQuery);
			if (results.next()){
				results.close();
				return genAnswer(stat,"SELECT distinct course_name from course where course_level = \"" + tmpkeyWord + "\"","course_name","No course found with level " + tmpkeyWord);
			}
			results.close();

			//credits
			selectQuery = "Select * from course where course_credits = \"" + tmpkeyWord + "\"";
			results = stat.executeQuery(selectQuery);
			if (results.next()){
				results.close();
				return genAnswer(stat,"SELECT distinct course_name from course where course_credits = \"" + tmpkeyWord + "\"","course_name","No course found with " + tmpkeyWord + " credits");
			}
			results.close();

			//program name
			selectQuery = "Select * from program where program_name = \"" + tmpkeyWord + "\"";
			results = stat.executeQuery(selectQuery);
			if (results.next()){
				results.close();
				return genAnswer(stat,"SELECT course_fk from program_course where program_fk = \"" + tmpkeyWord + "\"","course_fk","No courses for this program");
			}
			results.close();
			
			//program code
			selectQuery = "Select * from program where program_code = \"" + tmpkeyWord + "\"";
			results = stat.executeQuery(selectQuery);
			if (results.next()){
				results.close();
				return genAnswer(stat,"SELECT pc.course_fk from program_course pc, program p where pc.program_fk = p.program_name  AND p.program_code = \"" + tmpkeyWord + "\"","pc.course_fk","No courses for this program");
			}
			results.close();

			throw new NotFoundInDatabaseException("No course related to " + tmpkeyWord);
		} catch (SQLException e) {
			throw new NotFoundInDatabaseException("No course related to " + tmpkeyWord);
		} finally {
			msc.dbClose();
		}
	}
	
	// -----------------------------------------------------------------------------------------------------------------------
	/*
	 * this will return the code from a given Program, course and so on
	 * 
	 * @ismail
	 */
	public String fetchCount(){
		String selectQuery = "";
		ResultSet results = null;
		try {
			msc.dbConnect();
			Statement stat = msc.conn.createStatement();
			
			switch (env.getKeywordClass()){
			case Environ.COURSE:
				//teacher
				selectQuery = "Select * from teacher where teacher_name = \"" + tmpkeyWord + "\"";
				results = stat.executeQuery(selectQuery);
				if (results.next()){
					results.close();
					return genAnswer(stat,"SELECT count(*) as total from course where course_teacher_fk = \"" + tmpkeyWord + "\"","total","The teacher " + tmpkeyWord + " doesn't teach any course");
				}
				results.close();
				
				//program name
				selectQuery = "Select * from program where program_name = \"" + tmpkeyWord + "\"";
				results = stat.executeQuery(selectQuery);
				if (results.next()){
					results.close();
					return genAnswer(stat,"SELECT count(*) as total from program_course where program_fk = \"" + tmpkeyWord + "\"","total","No courses for this program");
				}
				results.close();
				
				//program code
				selectQuery = "Select * from program where program_code = \"" + tmpkeyWord + "\"";
				results = stat.executeQuery(selectQuery);
				if (results.next()){
					results.close();
					return genAnswer(stat,"SELECT count(*) as total from program_course pc, program p where pc.program_fk = p.program_name  AND p.program_code = \"" + tmpkeyWord + "\"","total","No mandatory courses for this program");
				}
				results.close();
				
				//roooms?			
				break;
			case Environ.ROOM:
				// if keyword is Building
				selectQuery = "Select * from building where building_name = \"" + tmpkeyWord + "\"";
				results = stat.executeQuery(selectQuery);
				if (results.next()){
					results.close();
					return genAnswer(stat,"SELECT count(*) as total from room where building_fk = \"" + tmpkeyWord + "\"","total",tmpkeyWord  + " is a building");
				}
				results.close();
				break;
			case Environ.PROGRAM:
				break;
			case Environ.BUILDING:
				// if keyword is campus
				selectQuery = "Select * from campus where campus_name = \"" + tmpkeyWord + "\"";
				results = stat.executeQuery(selectQuery);
				if (results.next()){
					results.close();
					return genAnswer(stat,"SELECT count(*) as total FROM building where campus_fk = \"" + tmpkeyWord + "\"","total","This campus doesn't have any buildings");
				}
				results.close();
				break;
			case Environ.MANDATORY_COURSE:
				//program
				selectQuery = "Select * from program where program_name = \"" + tmpkeyWord + "\"";
				results = stat.executeQuery(selectQuery);
				if (results.next()){
					results.close();
					return genAnswer(stat,"SELECT count(*) as total from program_course where mandatory = \"Yes\" AND program_fk = \"" + tmpkeyWord + "\"","total","No mandatory courses for this program");
				}
				results.close();
				//program
				selectQuery = "Select * from program where program_code = \"" + tmpkeyWord + "\"";
				results = stat.executeQuery(selectQuery);
				if (results.next()){
					results.close();
					return genAnswer(stat,"SELECT count(*) as total from program_course pc, program p where pc.program_fk = p.program_name AND pc.mandatory = \"Yes\" AND p.program_code = \"" + tmpkeyWord + "\"","total","No mandatory courses for this program");
				}
				results.close();
				break;
			case Environ.OPTIONAL_COURSE:
				//program
				selectQuery = "Select * from program where program_name = \"" + tmpkeyWord + "\"";
				results = stat.executeQuery(selectQuery);
				if (results.next()){
					results.close();
					return genAnswer(stat,"SELECT count(*) as total from program_course where mandatory = \"No\" AND program_fk = \"" + tmpkeyWord + "\"","total","No optional courses for this program");
				}
				results.close();
				//program
				selectQuery = "Select * from program where program_code = \"" + tmpkeyWord + "\"";
				results = stat.executeQuery(selectQuery);
				if (results.next()){
					results.close();
					return genAnswer(stat,"SELECT count(*) as total from program_course pc, program p where pc.program_fk = p.program_name AND pc.mandatory = \"No\" AND p.program_code = \"" + tmpkeyWord + "\"","total","No optional courses for this program");
				}
				results.close();
				break;
			case Environ.CREDIT:
				//program
				selectQuery = "Select * from program where program_name = \"" + tmpkeyWord + "\"";
				results = stat.executeQuery(selectQuery);
				if (results.next()){
					results.close();
					return genAnswer(stat,"SELECT program_ects from program where program_name = \"" + tmpkeyWord + "\"","program_ects","No credits assiganed to " + tmpkeyWord);
				}
				results.close();
				
				//program
				selectQuery = "Select * from program where program_code = \"" + tmpkeyWord + "\"";
				results = stat.executeQuery(selectQuery);
				if (results.next()){
					results.close();
					return genAnswer(stat,"SELECT distinct program_ects from program where program_code = \"" + tmpkeyWord + "\"","program_ects","No credits assiganed to " + tmpkeyWord);
				}
				results.close();
				
				//course name
				selectQuery = "Select * from course where course_name = \"" + tmpkeyWord + "\"";
				results = stat.executeQuery(selectQuery);
				if (results.next()){
					results.close();
					return genAnswer(stat,"SELECT distinct course_credits from course where course_name = \"" + tmpkeyWord + "\"","course_credits","No credits assiganed to " + tmpkeyWord);
				}
				results.close();
				
				//course code
				selectQuery = "Select * from course where course_code = \"" + tmpkeyWord + "\"";
				results = stat.executeQuery(selectQuery);
				if (results.next()){
					results.close();
					return genAnswer(stat,"SELECT course_credits from course where course_code = \"" + tmpkeyWord + "\"","course_credits","No credits assiganed to " + tmpkeyWord);
				}
				results.close();
				break;
			case Environ.CAMPUS:
				// if keyword is campus
				selectQuery = "Select count(*) as total from campus";
				results = stat.executeQuery(selectQuery);
				if (results.next()){
					String ans = results.getString("total");
					results.close();
					return ans;
				}
				results.close();
				break;
			case Environ.LEVEL:
				break;
			default:
				throw new NotFoundInDatabaseException("We cannot count that");
			}
			msc.dbClose();
			
			throw new NotFoundInDatabaseException("No data found for " + tmpkeyWord);
		} catch (SQLException e) {
			msc.dbClose();
			throw new NotFoundInDatabaseException("No data found for " + tmpkeyWord);
		}
	}
	
	// -----------------------------------------------------------------------------------------------------------------------
	/*
	 * this will return the code from a given Program, course and so on
	 * 
	 * @ismail
	 */
	public String fetchCode(){
		String selectQuery = "";
		ResultSet results = null;
		try {
			msc.dbConnect();
			Statement stat = msc.conn.createStatement();
	
			//program name
			selectQuery = "Select * from program where program_name = \"" + tmpkeyWord + "\"";
			results = stat.executeQuery(selectQuery);
			if (results.next()){
				results.close();
				return genAnswer(stat,"SELECT program_code from program where program_name = \"" + tmpkeyWord + "\"","program_code","No code assiganed to " + tmpkeyWord);
			}
			results.close();
			
			//program code
			selectQuery = "Select * from program where program_code = \"" + tmpkeyWord + "\"";
			results = stat.executeQuery(selectQuery);
			if (results.next()){
				results.close();
				return tmpkeyWord;
			}
			results.close();
			
			//course name
			selectQuery = "Select * from course where course_name = \"" + tmpkeyWord + "\"";
			results = stat.executeQuery(selectQuery);
			if (results.next()){
				results.close();
				return genAnswer(stat,"SELECT course_code from course where course_name = \"" + tmpkeyWord + "\"","course_code","No code assiganed to " + tmpkeyWord);
			}
			results.close();
			
			//pcourse code
			selectQuery = "Select * from course where course_code = \"" + tmpkeyWord + "\"";
			results = stat.executeQuery(selectQuery);
			if (results.next()){
				results.close();
				return tmpkeyWord;
			}
			results.close();
			
			//teacher room code
			selectQuery = "Select * from teacher where teacher_name = \"" + tmpkeyWord + "\"";
			results = stat.executeQuery(selectQuery);
			if (results.next()){
				results.close();
				return genAnswer(stat,"SELECT r.room_code from teacher t, room r where t.room_fk = r.room_name AND t.teacher_name = \"" + tmpkeyWord + "\"","r.room_code","No code assiganed to " + tmpkeyWord);
			}
			results.close();
			
			//room name
			selectQuery = "Select * from room where room_name = \"" + tmpkeyWord + "\"";
			results = stat.executeQuery(selectQuery);
			if (results.next()){
				results.close();
				return genAnswer(stat,"SELECT room_code from room where room_name = \"" + tmpkeyWord + "\"","room_code","No code assiganed to " + tmpkeyWord);
			}
			results.close();
			
			//room code
			selectQuery = "Select * from room where room_code = \"" + tmpkeyWord + "\"";
			results = stat.executeQuery(selectQuery);
			if (results.next()){
				results.close();
				return tmpkeyWord;
			}
			results.close();	
			msc.dbClose();
			
			throw new NotFoundInDatabaseException("No code found for " + tmpkeyWord);
		} catch (SQLException e) {
			msc.dbClose();
			throw new NotFoundInDatabaseException("No code found for " + tmpkeyWord);
		}
	}
	

	// -----------------------------------------------------------------------------------------------------------------------
		/*
		 * this will the level from a given Program or course
		 * 
		 * @ismail
		 */
		public String fetchLevel(){
			String selectQuery = "";
			ResultSet results = null;
			try {
				msc.dbConnect();
				Statement stat = msc.conn.createStatement();

				//program
				selectQuery = "Select * from program where program_name = \"" + tmpkeyWord + "\"";
				results = stat.executeQuery(selectQuery);
				if (results.next()){
					results.close();
					return genAnswer(stat,"SELECT program_level from program where program_name = \"" + tmpkeyWord + "\"","program_level","No level assiganed to " + tmpkeyWord);
				}
				results.close();
				
				//program
				selectQuery = "Select * from program where program_code = \"" + tmpkeyWord + "\"";
				results = stat.executeQuery(selectQuery);
				if (results.next()){
					results.close();
					return genAnswer(stat,"SELECT distinct program_level from program where program_code = \"" + tmpkeyWord + "\"","program_level","No level assiganed to " + tmpkeyWord);
				}
				results.close();
				
				//course name
				selectQuery = "Select * from course where course_name = \"" + tmpkeyWord + "\"";
				results = stat.executeQuery(selectQuery);
				if (results.next()){
					results.close();
					return genAnswer(stat,"SELECT distinct course_level from course where course_name = \"" + tmpkeyWord + "\"","course_level","No level assiganed to " + tmpkeyWord);
				}
				results.close();
				
				//pcourse code
				selectQuery = "Select * from course where course_code = \"" + tmpkeyWord + "\"";
				results = stat.executeQuery(selectQuery);
				if (results.next()){
					results.close();
					return genAnswer(stat,"SELECT course_level from course where course_code = \"" + tmpkeyWord + "\"","course_level","No level assiganed to " + tmpkeyWord);
				}
				results.close();
				msc.dbClose();
				
				throw new NotFoundInDatabaseException("No level found for " + tmpkeyWord);
			} catch (SQLException e) {
				msc.dbClose();
				throw new NotFoundInDatabaseException("No level found for " + tmpkeyWord);
			}
		}
	
// -----------------------------------------------------------------------------------------------------------------------
		/*
		 * this will the level from a given Program or course
		 * 
		 * @ismail
		 */
		public String fetchCredits(){
			String selectQuery = "";
			ResultSet results = null;
			try {
				msc.dbConnect();
				Statement stat = msc.conn.createStatement();

				//program
				selectQuery = "Select * from program where program_name = \"" + tmpkeyWord + "\"";
				results = stat.executeQuery(selectQuery);
				if (results.next()){
					results.close();
					return genAnswer(stat,"SELECT program_ects from program where program_name = \"" + tmpkeyWord + "\"","program_ects","No credits assiganed to " + tmpkeyWord);
				}
				results.close();
				
				//program
				selectQuery = "Select * from program where program_code = \"" + tmpkeyWord + "\"";
				results = stat.executeQuery(selectQuery);
				if (results.next()){
					results.close();
					return genAnswer(stat,"SELECT distinct program_ects from program where program_code = \"" + tmpkeyWord + "\"","program_ects","No credits assiganed to " + tmpkeyWord);
				}
				results.close();
				
				//course name
				selectQuery = "Select * from course where course_name = \"" + tmpkeyWord + "\"";
				results = stat.executeQuery(selectQuery);
				if (results.next()){
					results.close();
					return genAnswer(stat,"SELECT distinct course_credits from course where course_name = \"" + tmpkeyWord + "\"","course_credits","No credits assiganed to " + tmpkeyWord);
				}
				results.close();
				
				//course code
				selectQuery = "Select * from course where course_code = \"" + tmpkeyWord + "\"";
				results = stat.executeQuery(selectQuery);
				if (results.next()){
					results.close();
					return genAnswer(stat,"SELECT course_credits from course where course_code = \"" + tmpkeyWord + "\"","course_credits","No credits assiganed to " + tmpkeyWord);
				}
				results.close();
				msc.dbClose();
				
				throw new NotFoundInDatabaseException("No credits found for " + tmpkeyWord);
			} catch (SQLException e) {
				msc.dbClose();
				throw new NotFoundInDatabaseException("No credits found for " + tmpkeyWord);
			}
		}
		
	// -----------------------------------------------------------------------------------------------------------------------
	/*
	 * this will get list of mandatory courses optional from a given Program
	 * 
	 * @ismail
	 */
	public String fetchMandatoryCourse() throws NotFoundInDatabaseException {
		String selectQuery = "";
		ResultSet results = null;

		try {
			msc.dbConnect();
			Statement stat = msc.conn.createStatement();

			if (tmpkeyWord==null)
				throw new ProgramNotFoundException("Please, specify the program of the mandatory courses");
			
			//program
			selectQuery = "Select * from program where program_name = \"" + tmpkeyWord + "\"";
			results = stat.executeQuery(selectQuery);
			if (results.next()){
				results.close();
				return genAnswer(stat,"SELECT course_fk from program_course where mandatory = \"Yes\" AND program_fk = \"" + tmpkeyWord + "\"","course_fk","No mandatory courses for this program");
			}
			results.close();
			
			//program
			selectQuery = "Select * from program where program_code = \"" + tmpkeyWord + "\"";
			results = stat.executeQuery(selectQuery);
			if (results.next()){
				results.close();
				return genAnswer(stat,"SELECT pc.course_fk from program_course pc, program p where pc.program_fk = p.program_name AND pc.mandatory = \"Yes\" AND p.program_code = \"" + tmpkeyWord + "\"","pc.course_fk","No mandatory courses for this program");
			}
			results.close();
			msc.dbClose();
			throw new NotFoundInDatabaseException("No mandatory courses found for " + tmpkeyWord);
		} catch (SQLException e) {
			msc.dbClose();
			throw new NotFoundInDatabaseException("No mandatory courses found for " + tmpkeyWord);
		}
	}

	// -----------------------------------------------------------------------------------------------------------------------
	/*
	 * this will get list of optional courses optional from a given Program
	 * 
	 * @ismail
	 */
	public String fetchOptionalCourse() throws NotFoundInDatabaseException {
		String selectQuery = "";
		ResultSet results = null;

		try {
			msc.dbConnect();
			Statement stat = msc.conn.createStatement();
			
			if (tmpkeyWord==null)
				throw new ProgramNotFoundException("Please, specify the program of the optional courses");
			
			//program
			selectQuery = "Select * from program where program_name = \"" + tmpkeyWord + "\"";
			results = stat.executeQuery(selectQuery);
			if (results.next()){
				results.close();
				return genAnswer(stat,"SELECT course_fk from program_course where mandatory = \"No\" AND program_fk = \"" + tmpkeyWord + "\"","course_fk","No optional courses for this program");
			}
			results.close();
			
			//program
			selectQuery = "Select * from program where program_code = \"" + tmpkeyWord + "\"";
			results = stat.executeQuery(selectQuery);
			if (results.next()){
				results.close();
				return genAnswer(stat,"SELECT pc.course_fk from program_course pc, program p where pc.program_fk = p.program_name AND pc.mandatory = \"No\" AND p.program_code = \"" + tmpkeyWord + "\"","pc.course_fk","No optional courses for this program");
			}
			results.close();
			throw new NotFoundInDatabaseException("No optional courses found for " + tmpkeyWord);
		} catch (SQLException e) {
			throw new NotFoundInDatabaseException("No optional courses found for " + tmpkeyWord);
		}finally{
			msc.dbClose();
		}
	}

	// test main method
	public static void main(String[] args) {
		Environ env = new Environ();

		env.setKeyword("DTA123");
		env.setVerbKeyword(Environ.VERB_IS);
		env.setTarget(Environ.MANDATORY_COURSE);
		env.setKeywordClass(Environ.CAMPUS);

		GetAnswerData gad = new GetAnswerData(env);

		try {
			System.out.print(gad.fetchRoom());
		} catch (NotFoundInDatabaseException e) {
			e.printStackTrace();
		}

	}

	

}
